package com.boda.common.util.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.transaction.support.TransactionTemplate;

import com.boda.common.util.fw.SpringHelper;
import com.boda.common.util.str.StringHelper;

public class DBHelper extends JdbcDaoSupport {
    private static Logger logger = Logger.getLogger(DBHelper.class);
    public TransactionTemplate transactionTemplate;
    public LobHandler lobHandler;
    public StringHelper str = new StringHelper();

    public LobHandler getLobHandler() {
        return lobHandler;
    }

    public void setLobHandler(LobHandler lobHandler) {
        this.lobHandler = lobHandler;
    }

    public synchronized static DBHelper getInstance() {
        DBHelper db = null;
        try {
            db = (DBHelper) SpringHelper.getBean("dbHelper");
        } catch (Exception e) {
            logger.error("创建数据操作对象失败！");
        }
        return db;
    }

    public synchronized static Connection getDBConnection() {
        Connection conn = null;
        DBHelper db = DBHelper.getInstance();
        try {
            conn = db.getConnection();
        } catch (Exception e) {
            logger.error("获取数据库Connection失败！", e);
        }
        return conn;
    }

    public Map<String, Object> queryForMap(String sql, Object[] objects) {
        Map<String, Object> map = null;
        try {
            map = this.getJdbcTemplate().queryForMap(sql, objects);
        } catch (EmptyResultDataAccessException e) {

        } catch (Exception e) {
            logger.error(e);
            logger.error(str.getSql(sql, objects));
        }
        if (map == null)
            map = new HashMap<String, Object>();
        return map;
    }

    public Map<String, Object> queryForMap(String sql) {
        return queryForMap(sql, null);
    }

    public List<Map<String, Object>> queryForList(String sql, Object[] objects) {
        List<Map<String, Object>> list = null;
        try {
            list = this.getJdbcTemplate().queryForList(sql, objects);
        } catch (Exception e) {
            logger.error(e);
        }
        if (list == null)
            list = new ArrayList<Map<String, Object>>();
        return list;
    }

    public List<Map<String, Object>> queryForList(String sql) {
        return queryForList(sql, null);
    }

    public List<Map<String, Object>> queryForPage(String sql, Object[] objects, int startIndex, int pageSize) {
        sql += " limit ? offset ? ";
        List<Object> lstParam = null;
        lstParam = new ArrayList<Object>();
        lstParam.addAll(Arrays.asList(objects));
        lstParam.add(pageSize);
        lstParam.add(startIndex);

        return queryForList(sql, lstParam.toArray());
    }

    public List<Map<String, Object>> queryForPage(String sql, int startIndex, int pageSize) {
        return queryForPage(sql, null, startIndex, pageSize);
    }

    public int update(String sql, Object[] objects) {
        int exc = 1;
        try {
            this.getJdbcTemplate().update(sql, objects);
        } catch (Exception e) {
            exc = 0;
            logger.error(e);
            logger.error(str.getSql(sql, objects));
        }
        return exc;
    }

    public int update(String sql) {
        return update(sql, null);
    }

    public int queryForInt(String sql, Object[] objects) {
        int exc = -1;
        try {
            exc = this.getJdbcTemplate().queryForObject(sql, objects, Integer.class);
        } catch (Exception e) {
            exc = -1;
            logger.error(e);
            System.out.println(e.toString());
        }
        return exc;
    }

    public int queryForInt(String sql) {

        return queryForInt(sql, null);
    }

    public TransactionTemplate getTransactionTemplate() {
        return transactionTemplate;
    }

    public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
        this.transactionTemplate = transactionTemplate;
    }

    public ProcHelper getProcHelper(String sql) {
        ProcHelper proc = null;
        try {
            proc = new ProcHelper(this.getDataSource(), sql);
            proc.setSql(sql);
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return proc;
    }

    public int updateClob(final String sql, final Object[] objects, final int[] clobIndex) {
        int exc = 1;
        try {
            getJdbcTemplate().execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
                protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
                    boolean clobFlag = false;
                    for (int i = 1; i <= objects.length; i++) {
                        Object param = objects[i - 1];
                        clobFlag = false;
                        for (int j = 0; j < clobIndex.length; j++) {

                            if (i == clobIndex[j]) {
                                clobFlag = true;
                                break;
                            }
                        }
                        if (clobFlag == true) {
                            lobCreator.setClobAsString(ps, i, (String) param);
                        } else {
                            ps.setObject(i, param);
                        }

                    }
                }
            });

        } catch (Exception e) {
            exc = 0;
            logger.error(e);
            System.out.println(e.toString());
        }
        exc = 1;
        return exc;

    }

}
